iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 21
0
Big Data

30天學會Data Integration - Kettle系列 第 21

Step - 將Excel資料寫入資料庫

  • 分享至 

  • xImage
  •  

此篇要介紹的Step是[Output]Insert/Update,此Step應該是我目前使用次數最多的Step,因為資料整合到最後大多的情況都還是會寫入或更新資料庫

[Output]Insert/Update介紹

此Step包含兩種功能,一種寫入資料,另一種是更新資料,設定方式會稍有不同,此Step會根據我們所下的條件,去資料表中檢查是否存在符合條件的資料,若有找到則更新此筆資料,若找不到則進行寫入

本篇目標

資料來源使用Northwind資料庫中的Shippers資料表,將對以下三筆資料進行電話的更新
http://ithelp.ithome.com.tw/upload/images/20170105/20091626NQ9Vu28NQU.png

將想要進行更新的電話與新增的資料準備在Excel檔案裡面,透過Kettle來幫我們完成更新與寫入資料庫的動作
http://ithelp.ithome.com.tw/upload/images/20170105/20091626jQQzAezCLb.png

新增 Microsoft Excel Input

http://ithelp.ithome.com.tw/upload/images/20170105/200916260rf23Be1vk.png

設定 Microsoft Excel Input

選擇檔案,不熟的可以複習這篇Step - 讀取Excel檔案
http://ithelp.ithome.com.tw/upload/images/20170105/20091626u0YoKuTwqz.png

選擇欄位
http://ithelp.ithome.com.tw/upload/images/20170105/20091626zK6CNLQHBk.png

新增 Insert/Update

請於Output資料夾中找到Insert/Update,拖曳到主要編輯區,並建立Hop
http://ithelp.ithome.com.tw/upload/images/20170105/20091626v4iThbUjuW.png

設定 Insert/Update

1 請選擇要將資料新增或寫入哪個table
2 請設定搜尋資料的條件,在這邊我們以Table中的ShipperID欄位和Excel中的ID欄位來比對,比對結果有兩種:
-符合這個條件的資料,則位於下方的更新欄位中值會被更新
-不符合這個條件的資料,則會被寫入Table,也就是在table中新增一筆資料的意思
3 設定要更新的欄位,可以選擇Edit mapping來設定欄位的對應,有時候欄位名稱不一定都相同,可透過這個介面來進行設定
http://ithelp.ithome.com.tw/upload/images/20170105/20091626sOHP4kEJaG.png
Don't perform any updates:如果不想進行資料的更新,單純只想要寫入資料,請記得勾選此項目(本篇的例子是不需要勾選的)

預覽或執行 Insert/Update

完成設定之後,我們就可以來預覽或是執行,但這邊有件事情非常重要,就是 Insert/Update 是不能Rollback的,
Insert/Update 不能 Rollback
Insert/Update 不能 Rollback
Insert/Update 不能 Rollback
所以你一旦按下預覽或執行,資料庫的資料就會被更改,然後就一去不復返了... 所以使用這個Step要非常的小心,建議先在測試機的資料庫進行確認,沒有錯誤再去更新正式機的資料

http://ithelp.ithome.com.tw/upload/images/20170105/20091626nln7QwkFsp.png

確認資料表

完成更新與寫入
http://ithelp.ithome.com.tw/upload/images/20170105/200916268zY93b9U8K.png

補充:錯誤處理

有人想過如果資料寫入錯誤會發生什麼事情嗎?先來了解一下Shippers資料表的結構,我們可以看到Phone只能存24個字
http://ithelp.ithome.com.tw/upload/images/20170105/20091626sxPF1twWIH.png

有3筆更新資料,故意把第一筆的電話輸入超過24個字,那再Insert/Update會發生什麼事呢
http://ithelp.ithome.com.tw/upload/images/20170105/20091626IqULSWo5ny.png

執行或預覽時,肯定是報錯,查看Step Metrics頁籤
http://ithelp.ithome.com.tw/upload/images/20170105/20091626CLuXjRgeQg.png

查看Logging頁籤
http://ithelp.ithome.com.tw/upload/images/20170105/200916263hWD5o3xJc.png

此時整個Transformation就中止了,有時候我們可能會不想馬上去處理那些有問題的資料,所以我們可以使用Hop來幫我們過濾成功與失敗的資料,馬上動手新增兩個Dummy並建立Hop,此時會發現再建立Hop時,會出現選單,此時的Error handling of step就是拿來蒐集失敗的資料
http://ithelp.ithome.com.tw/upload/images/20170105/20091626j7EmA3IbOY.png

完成Hop設定之後再執行Transformation,就不會抱錯了,而且可以看到Dummy (do nothing) 2接受到一筆更新失敗的資料,有了這個錯誤處理的機制,後續再驗證資料轉置是否全部正確時就非常方便
http://ithelp.ithome.com.tw/upload/images/20170105/20091626YUdCF8s5de.png


其實MSSQL也有提供EXCEL匯入資料表的功能,所以單看這個例子是看不出來使用Kettle有什麼優點,因為此例子沒有欄位是需要進行資料處理或分析的,一旦有資料處理的需求,那很顯然MSSQL提供EXCEL匯入的功能就相對稍嫌不足了,也或許有人會覺得才3筆資料我自己用手打還比較快!但如果資料量到300筆或3000筆就不會想自己慢慢複製貼上了吧XD


上一篇
Step - 一對一查詢
下一篇
Step - 取得系統資訊並寫入資料庫
系列文
30天學會Data Integration - Kettle30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言